<?php
// 数据统计模型
class StatisticsService extends Service {
	
	/**
	 * 计算商品卖出量 -- 若总店：查询所有  分店则查询验证的总数
	 * @param unknown_type $pid
	 */
	public function countGoodsSellByPid($pid){
		$isroot = D('Partner','Admin')->isRootPartner($pid);
		if($isroot){
			$gdata = D('Goods','Admin')->selectGoodsInfo(array('pid'=>$pid,'gid'=>$gid),'id');
			if(!$ifpage)$ary_gids = getSubByKey($gdata,"id");
			else $ary_gids = getSubByKey($gdata['data'],"id");
			$map['gid']=$ary_gids;
			if(count($map['gid'])==0)return 0;//为了避免$map['gid']为空时,查询条件忽略该条件.查询出所有数据.
			
		}else{
			$map['pid']=$pid;
			if($pid==null)return 0;//为了避免$map['pid']为空时,查询条件忽略该条件.查询出所有数据.
		}
		
		$map['is_used']='Y';
		$map['is_canceled']='N';
		$sum = model('Coupon')->countQuantity($map);
		if($sum==null)$sum=0;
		return $sum;
	}

	
	/**
	 * 统计商品被商户验证的数量
	 * 返回数据均已商户作为X轴方向。如需改变方向设置array('partner_title'=>'xlabel','statflag'=>'ylabel')xlabel与ylabel呼唤即可。
	 * @param unknown_type $map			
 	 *					pid				商户id  
 	 *					checker			要统计的checkerID
 	 *					gid				商品id组成的数组
 	 *					gsort			商品分类
	 * 			 		stattype		统计方式  time按时间统计  或计算总数    默认计算总数
	 * 					timeexp			按时间统计的表达式默认：'%Y-%m'按月统计			stattype=time时生效
	 * 					timeInterval	时间区间，查询所在的时间范围  单位秒  stime-开始时间   etime-结束时间				stattype=time时生效
	 * 											如果etime与stime均不存在 则当前时间后推5个月内的数据
	 * 											如果etime存在 stime不存在则etime后退5个月内的数据  反之向前推5个月
	 * 
	 * 					rtntype			数据返回格式chart返回表格使用的数据格式，normal返回数组 默认 Model查询结果
	 * 
	 * @param unknown_type $ordertype	排序方式  new商品录入时间降  down销售量降  up销售量升
	 * @param unknown_type $ifpage		是否分页查询
	 * @param unknown_type $limit		分页查询时查询每页数据条数    非分页查询的数据条数
	 */
	public function statGoods4Checked($map,$ordertype=null,$ifpage=false,$limit=null){
		$interval = 2;//表示查询时默认情况的时间间隔，单位月。
		//如果gid为空，则查询pid对应的商品进行统计。
		if(empty($map['gid'])){
			$rootPid = D('Partner','Admin')->getRootPid($map['pid']);
			//排序语句
			$gorder = " `ctime`, `checked_number` desc ";
			if($ordertype=='new')$gorder = " `ctime`, `checked_number` desc ";
			if($ordertype=='down')$gorder = " `checked_number` desc ";
			if($ordertype=='up')$gorder = " `checked_number` asc ";
			$gdata = D('Goods','Admin')->selectGoodsInfo(array('pid'=>$rootPid,'gsort'=>$map['gsort']),'id',$gorder,$limit,$ifpage);
			Log::record('StatisticsService.statGoods4Checked()gdata='.parseAry2Str($gdata),Log::DEBUG,Log::FILE);
			if(!$ifpage)$ary_gids = getSubByKey($gdata,"id");
			else $ary_gids = getSubByKey($gdata['data'],"id");
		}else{
			$ary_gids[]=$map['gid'];
		}
		if(!empty($map['checker'])&&count($map['checker'])>0)$where_checker =  " and c.`checker_id` in ( '".implode("','",$map['checker'])."') ";
		//统计相关语句
		if( $map['stattype']=='time' ){
			//$statfield = " FROM_UNIXTIME( tmp.`coupon_sendtime`,'%Y-%m') as `statflag` ";//进行统计分类的字段
			$stime = intval($map['timeInterval']['stime']);$etime = intval($map['timeInterval']['etime']);
			if( (empty($stime)&&empty($etime))||$etime<$stime){
				$etime = time();
				$stime = strtotime("-".$interval." months", $etime);
			}else{
				if(empty($stime)){
					$stime = strtotime("-".$interval." months", $etime);
				}
				if(empty($etime)){
					$etime = strtotime("+".$interval." months", $stime);
				}
			}
			$where_time = " and c.check_time >= ".$stime." and c.check_time <= ".$etime;
			if($map['timeexp']=='day')$map['timeexp']='%Y-%m-%d';
			if($map['timeexp']=='month')$map['timeexp']='%Y-%m';
			if(empty($map['timeexp']))$map['timeexp']='%Y-%m';
			$statfield = " FROM_UNIXTIME( c.`check_time`,'".$map['timeexp']."') as `statflag` ";
		}else{
			$statfield = " '总数' as `statflag` ";
		}
		
		$prefix = C('DB_PREFIX');
		$wherec = " c.`goods_id` in ( '".implode("','",$ary_gids)."') and c.`is_send`='Y' and c.`is_canceled`='N' and c.`checker_id` > 0 ".$where_time." ".$where_checker;
		$where = " vg.`id` in ( '".implode("','",$ary_gids)."') ";
		

		//排序语句
		$order = " order by `goods_ctime`, `sum` desc ";
		if($ordertype=='new')$order = " order by `goods_ctime`, `sum` desc ";
		if($ordertype=='down')$order = " order by `sum` desc ";
		if($ordertype=='up')$order = " order by `sum` asc ";
		
		$sql = 	" select vg_tmp.*,c_tmp.* from ( select vg.id as goods_id , vg.title as goods_title ,vg.sort_id as goods_sort_id , vg.goods_icon as goods_icon , vg.start_time as goods_start_time ,vg.ctime as goods_ctime from `{$prefix}goods` vg where ".$where." ) as vg_tmp ".
				" left join ( select c.checker_id as coupon_checker_id , c.goods_id as coupon_goods_id , sum(c.`quantity`) as `sum` , ".$statfield." from `{$prefix}coupon` c where ".$wherec."  group by goods_id , statflag ) c_tmp ".
				" on c_tmp.coupon_goods_id = vg_tmp.goods_id ";
		
		$statdata = M('')->query($sql.$order);
		Log::record('StatisticsService.statGoods4Checked()sql='.(M('')->getLastSql()),Log::DEBUG,Log::FILE);
		Log::save(); 
		//dump($statdata);
		if( $map['rtntype']=='chart' ){
			$statdata = $this->getChartData($statdata,array('sum','statflag','goods_title','coupon_goods_id','goods_id'),array('goods_title'=>'xlabel','statflag'=>'ylabel')
									,$map['timeexp'],$stime,$etime);
		}else{
		}
		if($ifpage) $statdata['pagenavhtml'] = $gdata['html'];
		return $statdata;
	}
	
	
	
	/**
	 * 商户（和旗下分店）总销售统计
	 * 返回数据均已商户作为X轴方向。如需改变方向设置array('partner_title'=>'xlabel','statflag'=>'ylabel')xlabel与ylabel呼唤即可。
	 * @param array $map		
	 * 					pid				商户ID 
	 * 					checker			要统计的checkerID
	 * 					psort			商户类型         
	 * 			 		stattype		统计方式  time按时间统计  venues店面场地统计    
	 * 					timeexp			按时间统计的表达式默认：'%Y-%m'按月统计			stattype=time时生效
	 * 					timeInterval	时间区间，查询所在的时间范围  单位秒  stime-开始时间   etime-结束时间				stattype=time时生效
	 * 											如果etime与stime均不存在 则当前时间后推5个月内的数据
	 * 											如果etime存在 stime不存在则etime后退5个月内的数据  反之向前推5个月
	 * 
	 * 					rtntype			数据返回格式chart返回表格使用的数据格式，normal返回数组 默认 Model查询结果
	 * 
	 * 
	 * @param unknown_type $ordertype	排序方式  new商品录入时间降  down销售量降  up销售量升
	 * @param unknown_type $ifpage		是否分页查询
	 * @param unknown_type $limit		分页查询时查询每页数据条数    非分页查询的数据条数
	 */
	public function statPartner4Checked($map,$ordertype=null,$ifpage=false,$limit=null){
		$interval = 2;//表示查询时默认情况的时间间隔，单位月。
		Log::record('StatisticsService.statPartner4Checked()->请求参数='.parseAry2Str($map),Log::DEBUG,Log::FILE);
		
		if(empty($map['checker'])||count($map['checker'])<1){
			$porder = " `ctime`, `checked_number` desc ";
			if($ordertype=='new')$porder = " `ctime`, `checked_number` desc ";
			if($ordertype=='down')$porder = " `checked_number` desc ";
			if($ordertype=='up')$porder = " `checked_number` asc ";
			//$pdata = D('Partner','Admin')->selectPartnerInfo(array('porppid'=>$map['pid'],'psort'=>$map['psort']),'id',$porder,$limit,$ifpage);
			$pdata = D('Partner','Admin')->selectPartnerInfo(array('porppid'=>$map['pid'],'psort'=>$map['psort'],'noroot'=>'yes'),'id',$porder,$limit,$ifpage);
			
			Log::record('StatisticsService.statPartner4Checked()->获得的商户信息='.parseAry2Str($pdata),Log::DEBUG,Log::FILE);
			if(!$ifpage)$ary_pids = getSubByKey($pdata,"id");
			else $ary_pids = getSubByKey($pdata['data'],"id");
		}else{
			$ary_pids = $map['checker'];
		}
		if(empty($map['gid'])){
			$rootPid = D('Partner','Admin')->getRootPid($map['pid']);
			$gdata = D('Goods','Admin')->selectGoodsInfo(array('pid'=>$rootPid),'id');
			$ary_gids = getSubByKey($gdata,"id");
		}else{
			$ary_gids[] = $map['gid'];
		}
		Log::record('StatisticsService.statPartner4Checked()->参数ary_pids='.parseAry2Str($ary_pids),Log::DEBUG,Log::FILE);
		
		if(!empty($map['checker'])&&count($map['checker'])>0)$where_checker =  " and c.`checker_id` in ( '".implode("','",$map['checker'])."')";
		
		//统计相关语句
		if( $map['stattype']=='time' ){
			//$statfield = " FROM_UNIXTIME( tmp.`coupon_sendtime`,'%Y-%m') as `statflag` ";//进行统计分类的字段
			$stime = intval($map['timeInterval']['stime']);$etime = intval($map['timeInterval']['etime']);
			if( (empty($stime)&&empty($etime))||$etime<$stime){
				$etime = time();
				$stime = strtotime("-".$interval." months", $etime);
			}else{
				if(empty($stime)){
					$stime = strtotime("-".$interval." months", $etime);
				}
				if(empty($etime)){
					$etime = strtotime("+".$interval." months", $stime);
				}
			}
			$where_time = " and c.check_time >= ".$stime." and c.check_time <= ".$etime;
			if($map['timeexp']=='day')$map['timeexp']='%Y-%m-%d';
			if($map['timeexp']=='month')$map['timeexp']='%Y-%m';
			if(empty($map['timeexp']))$map['timeexp']='%Y-%m';
			$statfield = " FROM_UNIXTIME( c.`check_time`,'".$map['timeexp']."') as `statflag` ";
		}else{
			$statfield = " '总数' as `statflag` ";
		}
		
		$prefix = C('DB_PREFIX');
		$wherec = " c.`goods_id` in ( '".implode("','",$ary_gids)."') and c.`is_send`='Y' and c.`is_canceled`='N' and c.`checker_id` > 0 ".$where_time." ".$where_checker;
		$where = " vg.`id` in ( '".implode("','",$ary_pids)."') ";
		

		//排序语句
		$order = " order by `partner_ctime`, `sum` desc ";
		if($ordertype=='new')$order = " order by `partner_ctime`, `sum` desc ";
		if($ordertype=='down')$order = " order by `sum` desc ";
		if($ordertype=='up')$order = " order by `sum` asc ";
		
		$sql = 	" select vg_tmp.*,c_tmp.* from ( select vg.id as partner_id , vg.title as partner_title ,vg.location as partner_location,vg.address as partner_address,vg.phone as partner_phone ,vg.mobile as partner_mobile, vg.parent_id as partner_parent_id,vg.brand_icon as partner_brand_icon ,vg.ctime as partner_ctime from `{$prefix}partner` vg where ".$where." ) as vg_tmp ".
				" left join ( select c.checker_id as coupon_checker_id , sum(c.`quantity`) as `sum` , ".$statfield." from `{$prefix}coupon` c where ".$wherec."  group by checker_id , statflag ) c_tmp ".
				" on c_tmp.coupon_checker_id = vg_tmp.partner_id ";
		
		$statdata = M('')->query($sql.$order);
		Log::record('StatisticsService.statPartner4Checked()sql='.(M('')->getLastSql()),Log::DEBUG,Log::FILE);
		Log::save(); 
		//dump($statdata);
		if( $map['rtntype']=='chart' ){
			
			$statdata = $this->getChartData($statdata,array('sum','statflag','partner_title','coupon_checker_id','partner_id'),array('partner_title'=>'xlabel','statflag'=>'ylabel')
									,$map['timeexp'],$stime,$etime);
									
		}else{
			
		}
		if($ifpage) $statdata['pagenavhtml'] = $pdata['html'];
		return $statdata;
	}
	
	
	
	/**
	 * 转换成chart格式数据。
	 * @param unknown_type $statdata_
	 * @param unknown_type $key
	 * @param unknown_type $newkey
	 * @param unknown_type $timeexp_
	 * @param unknown_type $stime_
	 * @param unknown_type $etime_
	 */
	public function getChartData($statdata_,$key,$newkey,$timeexp_,$stime_,$etime_){
		if($timeexp_=='%Y-%m-%d'){
			$formatExp 	='Y-m-d';
			$addExp		= "+1 days";
		}else{
			$formatExp ='Y-m';
			$addExp		= "+1 months";
		}
		
		$statdata = getNewFromArray($statdata_,$key,$newkey);
		if(!empty($timeexp_)&&$etime_>$stime_){
			$etime = paramMillisecond2Date($etime_,$formatExp);
			$stime = paramMillisecond2Date($stime_,$formatExp);
			$index = 0;
			while (true){
				$index++;
				$statsort[]=$stime;
				if($etime==$stime||$index>60){
					break;
				}
				$stime = date($formatExp, strtotime($addExp, strtotime($stime)) );
				
			}
		}else{
			$statsort = array('总数');
		}
		$result = array('statdata'=>$statdata,'statsort'=>$statsort);
		//dump($result);
		return $result;
	}

	
	//服务初始化
	public function init($data = '') {
	}
	
	
	//运行服务，系统服务自动运行
	public function run() {
	}
}
?>